import numpy as np
from flask import Flask, jsonify ,request
import mysql.connector
from flasgger import Swagger
from flask_cors import CORS
app = Flask(__name__)
CORS(app)
swagger = Swagger(app)

# 数据库连接配置
def get_db_connection():
    return mysql.connector.connect(
        host='101.34.81.209',
        user='root',
        password='w1234567890',
        database='data_echarts'
    )

# 获取城市分布表数据
# 获取城市分布表数据，并按位置统计数量
@app.route('/get_city_distribution', methods=['GET'])
def get_city_distribution():
    """
           获取城市分布表数据
           ---
           responses:
               200:
                   description: 返回一个包含消息的JSON对象
                   schema:
                       type: object
                       properties:
                           message:
                               type: string
                               example: "Hello, World!"
           """
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        cursor.execute("SELECT location, COUNT(*) as total_count FROM distribution_of_cities_location_table GROUP BY location")
        data = cursor.fetchall()
        result = [{"name": name, "value": value} for name, value in data]
        cursor.close()
        conn.close()
        return jsonify(result)
    except Exception as e:
        return jsonify({"error": str(e)}), 500

# 获取报销数据表数据
# 获取报销数据表数据
@app.route('/get_expense_data', methods=['GET'])
def get_expense_data():
    """
           获取报销数据表数据
           ---
           responses:
               200:
                   description: 返回一个包含消息的JSON对象
                   schema:
                       type: object
                       properties:
                           message:
                               type: string
                               example: "Hello, World!"
           """
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        # 获取所有不同的报销类型
        cursor.execute("SELECT DISTINCT type FROM expense_data")
        types = [row[0] for row in cursor.fetchall()]
        # 获取所有不同的年份
        cursor.execute("SELECT DISTINCT year FROM expense_data")
        years = [str(row[0]) for row in cursor.fetchall()]
        # 构建返回数据结构
        result = {
            "expense": {
                "title": "报销数据",
                "base": 310,
                "unit": "元",
                "data": []
            },
            "common": {
                "year": years
            },
            "type": [{
                "key": "expense",
                "text": "报销数据"
            }]
        }
        for type_name in types:
            data_list = ["0"] * len(years)
            cursor.execute("SELECT year, count FROM expense_data WHERE type = %s", (type_name,))
            for row in cursor.fetchall():
                year_index = years.index(str(row[0]))
                data_list[year_index] = str(row[1])
            result["expense"]["data"].append({"name": type_name, "data": data_list})
        cursor.close()
        conn.close()
        return jsonify(result)
    except Exception as e:
        return jsonify({"error": str(e)}), 500

# 获取是否购买医保统计表数据
@app.route('/get_medical_insurance_status', methods=['GET'])
def get_medical_insurance_status():
    """
           获取是否购买医保统计表数据
           ---
           responses:
               200:
                   description: 返回一个包含消息的JSON对象
                   schema:
                       type: object
                       properties:
                           message:
                               type: string
                               example: "Hello, World!"
           """
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        cursor.execute("""
                SELECT year, SUM(count) as total_count
                FROM has_medical_insurance_table
                WHERE is_buy = '已购买'
                GROUP BY year
                order by year 
            """)
        data = cursor.fetchall()
        result = [{"name": f"{year}年", "value": value} for year, value in data]
        cursor.close()
        conn.close()
        return jsonify(result)
    except Exception as e:
        return jsonify({"error": str(e)}), 500

# 获取健康程度统计表数据
# 获取健康程度统计表数据，并按健康程度统计数量
@app.route('/get_health_level_data', methods=['GET'])
def get_health_level_data():
    """
           获取健康程度统计表数据
           ---
           responses:
               200:
                   description: 返回一个包含消息的JSON对象
                   schema:
                       type: object
                       properties:
                           message:
                               type: string
                               example: "Hello, World!"
           """
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        cursor.execute("SELECT level, COUNT(*) as total_count FROM health_level_table GROUP BY level")
        data = cursor.fetchall()
        result = [{"name": name, "sales": value} for name, value in data]
        cursor.close()
        conn.close()
        return jsonify(result)
    except Exception as e:
        return jsonify({"error": str(e)}), 500

# 获取受教育程度统计表数据
# 获取受教育程度统计表数据，并按教育程度统计数量
@app.route('/get_education_level_data', methods=['GET'])
def get_education_level_data():
    """
           获取受教育程度统计表数据
           ---
           responses:
               200:
                   description: 返回一个包含消息的JSON对象
                   schema:
                       type: object
                       properties:
                           message:
                               type: string
                               example: "Hello, World!"
           """
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        cursor.execute("SELECT education, COUNT(*) as total_count FROM population_education_level_proportion_table GROUP BY education")
        data = cursor.fetchall()
        result = [{"name": name, "value": value} for name, value in data]
        cursor.close()
        conn.close()
        return jsonify(result)
    except Exception as e:
        return jsonify({"error": str(e)}), 500

# 获取医保缴费总额表数据
@app.route('/get_medicare_contribution_data', methods=['GET'])
def get_medicare_contribution_data():
    """
       获取医保缴费总额表数据
       ---
       responses:
           200:
               description: 返回一个包含消息的JSON对象
               schema:
                   type: object
                   properties:
                       message:
                           type: string
                           example: "Hello, World!"
       """
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        cursor.execute("SELECT year, sum(count) FROM total_medicare_contribution_data GROUP BY year")
        data = cursor.fetchall()
        cursor.close()
        conn.close()
        return jsonify(data)
    except Exception as e:
        return jsonify({"error": str(e)}), 500


# 登录接口
@app.route('/login', methods=['POST'])
def login():
    """
    登录接口
    ---
    parameters:
      - name: body
        in: body
        required: true
        schema:
          type: object
          properties:
            username:
              type: string
            password:
              type: string
    responses:
      200:
        description: 登录成功返回成功消息，登录失败返回错误消息
        schema:
          type: object
          properties:
            message:
              type: string
    """
    data = request.get_json()
    username = data.get('username')
    password = data.get('password')

    if username == 'admin' and password == '123456':
        return jsonify({"code":0 , "message": "登录成功"})
    else:
        return jsonify({"code":1 ,"message": "账号或密码错误"}), 401


if __name__ == '__main__':
    app.run(debug=True)